﻿Option Explicit
Function action
	Dim objConnection,objCommand,strConnectionString
	strConnectionString = "Provider=SQLOLEDB;DRIVER=SQL Server;Server=192.168.33.252\WINCC;UID=sa;PWD=password;DATABASE=master;" 
	Set objConnection = CreateObject("ADODB.Connection")
	objConnection.ConnectionString = strConnectionString
	objConnection.Open
	CollectJCJ(objConnection)
	CollectJHJ(objConnection)
	CollectWaitingOrder(objConnection)
	CollectTeam(objConnection)
	CollectHighLowDevice(objConnection)
	CollectHumTem(objConnection)
	'Set objCommand = Nothing
	objConnection.Close
	'Set objRecordset = Nothing
	Set objConnection = Nothing
End Function

Sub CollectJCJ(objConnection)
	Dim s
	s = "ProLine1,ProLine2,OrderNum1,OrderNum2,OrderNum3,OrderNum4,BalScale1,BalScale2,BalScale3,BalRCount1,BalRCount2,BalRCount3,WLJRev,JTemOff1,JTemOff2,JTemOff3,JTemOff4,JTemOff5,JTemOff6,JTemOff7,JTemOff8,JTemOff9,JTemOff10,JTemSet1,JTemSet2,JTemSet3,JTemSet4,JTemSet5,JTemSet6,JTemSet7,JTemSet8,JTemSet9,JTemSet10,JTemReal1,JTemReal2,JTemReal3,JTemReal4,JTemReal5,JTemReal6,JTemReal7,JTemReal8,JTemReal9,JTemReal10,JMaxRev,JMinRev,JRealRev,JMaxCur,JMinCur,JRealCur,Ia,Ib,Ic,power,degreeH,degreeL,MaxRZ,MinRZ,RealRz,MaxRemn,MinRemn,RealRemn,JsProduce"
	Dim virList,tmp,strVar
	virList = Split(s,",",-1)
	Dim prefix,dt,strSQL,i
	For i = 1 To 16
		prefix = "JC" & i
		tmp = HMIRuntime.Tags(prefix&"_ProLine1").Read
		If tmp <> Empty Then
			' 只是用于测试
			'If i = 5 Then
			'	TestDegree(prefix&"_degreeL")
			'End If
		
			Set objCommand = CreateObject("ADODB.Command")
			objCommand.ActiveConnection = objConnection
			dt = Now()
			strSQL = "INSERT INTO [winccSQL].[dbo].[JCVariable] (ProLineCode,Time," & s & ") VALUES ("
			strSQL = strSQL+"'"& prefix &"'"
			strSQL = strSQL+",'"& dt &"'"
			Dim j
			For j = 0 To UBound(virList)
				strVar = prefix&"_"&virList(j)
				'HMIRuntime.Trace strVar & "  "
				tmp = HMIRuntime.Tags(strVar).Read
				If tmp = Empty Then
					tmp = 0
				End If
				'HMIRuntime.Trace tmp & vbCrLf
				strSQL=strSQL+","& tmp &""
			Next
			strSQL = strSQL+")"
			'HMIRuntime.Trace strSQL
			objCommand.CommandText = strSQL
			objCommand.Execute
			Set objCommand = Nothing
		End If
	Next
End Sub

Sub CollectJHJ(objConnection)
	Dim s
	s = "ProLine1,ProLine2,OrderNum1,OrderNum2,OrderNum3,OrderNum4,ZDTemper,ARealTem,ABStartT,BHeatST,BThrowST,UnitPC,UnitTC,PJobSP,Ia,Ib,Ic,power,degree"
	Dim virList,tmp,strVar
	virList = Split(s,",",-1)
	Dim prefix,dt,strSQL,i
	For i = 1 To 16
		prefix = "JH" & i
		tmp = HMIRuntime.Tags(prefix&"_ProLine1").Read
		If tmp <> Empty Then
			' 只是用于测试
			'If i = 5 Then
			'	TestDegree(prefix&"_degreeL")
			'End If
			
			Set objCommand = CreateObject("ADODB.Command")
			objCommand.ActiveConnection = objConnection
			dt = Now()
			strSQL = "INSERT INTO [winccSQL].[dbo].[JHVariable] (ProLineCode,Time," & s & ") VALUES ("
			strSQL = strSQL+"'"& prefix &"'"
			strSQL = strSQL+",'"& dt &"'"
			Dim j
			For j = 0 To UBound(virList)
				strVar = prefix&"_"&virList(j)
				'HMIRuntime.Trace strVar & "  "
				tmp = HMIRuntime.Tags(strVar).Read
				If tmp = Empty Then
					tmp = 0
				End If
				'HMIRuntime.Trace tmp & vbCrLf
				strSQL=strSQL+","& tmp &""
			Next
			strSQL = strSQL+")"
			'HMIRuntime.Trace strSQL
			objCommand.CommandText = strSQL
			objCommand.Execute
			Set objCommand = Nothing
		End If
	Next
End Sub

Sub CollectWaitingOrder(objConnection)
	Dim s
	s = "ProLine1,ProLine2,WOrderNum1,WOrderNum2,WOrderNum3,WOrderNum4"
	Dim virList,tmp,strVar
	virList = Split(s,",",-1)
	Dim prefix,dt,strSQL,i
	For i = 1 To 16
		prefix = "JC" & i
		tmp = HMIRuntime.Tags(prefix&"_ProLine1").Read
		If tmp <> Empty Then
			Set objCommand = CreateObject("ADODB.Command")
			objCommand.ActiveConnection = objConnection
			' 删除老的数据
			strSQL = "DELETE FROM [winccSQL].[dbo].[WaitingVariable] WHERE ProLineCode = '" & prefix & "'"
			objCommand.CommandText = strSQL
			objCommand.Execute
			' 添加新的数据
			dt = Now()
			strSQL = "INSERT INTO [winccSQL].[dbo].[WaitingVariable] (ProLineCode,Time," & s & ") VALUES ("
			strSQL = strSQL+"'"& prefix &"'"
			strSQL = strSQL+",'"& dt &"'"
			Dim j
			For j = 0 To UBound(virList)
				strVar = prefix&"_"&virList(j)
				'HMIRuntime.Trace strVar & "  "
				tmp = HMIRuntime.Tags(strVar).Read
				If tmp = Empty Then
					tmp = 0
				End If
				'HMIRuntime.Trace tmp & vbCrLf
				strSQL=strSQL+","& tmp &""
			Next
			strSQL = strSQL+")"
			'HMIRuntime.Trace strSQL
			objCommand.CommandText = strSQL
			objCommand.Execute
			Set objCommand = Nothing
		End If
	Next
End Sub

Sub CollectTeam(objConnection)
	Dim s
	s = "ProLine1,ProLine2,TeamName1,Mixinger1,Mixinger2,Extruder1,Extruder2,Packer1,Packer2,Qualer1,Qualer2"
	Dim virList,tmp,strVar
	virList = Split(s,",",-1)
	Dim prefix,dt,strSQL,i
	For i = 1 To 16
		prefix = "JC" & i
		tmp = HMIRuntime.Tags(prefix&"_ProLine1").Read
		If tmp <> Empty Then
			Set objCommand = CreateObject("ADODB.Command")
			objCommand.ActiveConnection = objConnection
			dt = Now()
			strSQL = "INSERT INTO [winccSQL].[dbo].[TeamVariable] (ProLineCode,Time," & s & ") VALUES ("
			strSQL = strSQL+"'"& prefix &"'"
			strSQL = strSQL+",'"& dt &"'"
			Dim j
			For j = 0 To UBound(virList)
				strVar = prefix&"_"&virList(j)
				'HMIRuntime.Trace strVar & "  "
				tmp = HMIRuntime.Tags(strVar).Read
				If tmp = Empty Then
					tmp = 0
				End If
				'HMIRuntime.Trace tmp & vbCrLf
				strSQL=strSQL+","& tmp &""
			Next
			strSQL = strSQL+")"
			'HMIRuntime.Trace strSQL
			objCommand.CommandText = strSQL
			objCommand.Execute
			Set objCommand = Nothing
		End If
	Next
End Sub

Sub CollectHighLowDevice(objConnection)
	Dim strDevices, strVars
	strDevices = "3D7_1,3D7_2,3D7_3,3D8_1,3D8_2,3D8_3,3D8_4,3D8_5,3D8_6,3D8_7,3D8_8,3D9_1,3D9_2,3D9_3,3D9_4,3D9_5,3D9_6,3D9_7,3D9_8,3D9_9,3D10_1,3D10_2,3D10_3,3D10_4,3D10_5,3D10_6,3D10_7,3D10_8,3D10_9,3D11_1,3D11_2,3D11_3,3D11_4,3D11_5,3D11_6,3D12_1,3D12_2,3D12_3,3D12_4,3D6_1,3D6_2,1D5_1,1D5_2,1D6_1,1D6_2,2D4_1,2D4_2,2D5_1,2D5_2"
	strVars = "Ia,Ib,Ic,Uab,Ubc,Uca,power,cos"
	Dim devList,virList,tmp,strVar
	devList = Split(strDevices,",",-1)
	virList = Split(strVars,",",-1)
	Dim prefix,dt,strSQL,i
	For i = 0 To UBound(devList)
		prefix = devList(i)
		tmp = HMIRuntime.Tags(prefix&"Ia").Read
		If tmp <> Empty Then
			Set objCommand = CreateObject("ADODB.Command")
			objCommand.ActiveConnection = objConnection
			dt = Now()
			strSQL = "INSERT INTO [winccSQL].[dbo].[HighLowVariable] (DeviceID,Time," & strVars & ") VALUES ("
			strSQL = strSQL+"'"& prefix &"'"
			strSQL = strSQL+",'"& dt &"'"
			Dim j
			For j = 0 To UBound(virList)
				strVar = prefix&virList(j)
				'HMIRuntime.Trace strVar & "  "
				tmp = HMIRuntime.Tags(strVar).Read
				If tmp = Empty Then
					tmp = 0
				End If
				'HMIRuntime.Trace tmp & vbCrLf
				strSQL=strSQL+","& tmp &""
			Next
			strSQL = strSQL+")"
			'HMIRuntime.Trace strSQL
			objCommand.CommandText = strSQL
			objCommand.Execute
			Set objCommand = Nothing
		End If
	Next
End Sub

Sub CollectHumTem(objConnection)
	Dim strVars
	strVars = "TEM_HUMHUM,TEM_HUMTEM"
	Dim virList,tmp,strVar
	virList = Split(strVars,",",-1)
	Dim dt,strSQL
	tmp = HMIRuntime.Tags("HUM").Read
	If tmp <> Empty Then
		Set objCommand = CreateObject("ADODB.Command")
		objCommand.ActiveConnection = objConnection
		dt = Now()
		strSQL = "INSERT INTO [winccSQL].[dbo].[HumTemVariable] (Time," & strVars & ") VALUES ("
		strSQL = strSQL+"'"& dt &"'"
		Dim j
		For j = 0 To UBound(virList)
			strVar = virList(j)
			'HMIRuntime.Trace strVar & "  "
			tmp = HMIRuntime.Tags(strVar).Read
			If tmp = Empty Then
				tmp = 0
			End If
			'HMIRuntime.Trace tmp & vbCrLf
			strSQL=strSQL+","& tmp &""
		Next
		strSQL = strSQL+")"
		'HMIRuntime.Trace strSQL
		objCommand.CommandText = strSQL
		objCommand.Execute
		Set objCommand = Nothing
	End If
End Sub

Sub TestDegree(varStr)
	Dim objTag
	Set objTag = HMIRuntime.Tags(varStr)
	objTag.Read
	objTag.Value = objTag.Value + 1
	objTag.Write
End S